package nl.hu.verbrandendephoenix.server;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;

import nl.hu.verbrandendephoenix.shared.User;

public class UserMethods {
	public static boolean validLogin(String loginKey, Connection con) {
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM gebruiker WHERE loginkey = '" + loginKey + "'");
			if(rs.next()) {
				return true;
			} else {
				return false;
			}
		} catch(SQLException sqle) {
			System.out.println("SQLException: " + sqle.getMessage());
			sqle.printStackTrace();
			return false;
		}
	}

	public static boolean createUser(String unm, String pwd, String eml, Connection con) {
		try {
			Statement stmt = con.createStatement();
			stmt.executeQuery("SELECT COUNT(username) FROM Gebruiker WHERE username = '"+unm+"'");
			ResultSet rs = stmt.getResultSet();
			if (rs.next() && rs.getInt(1) > 0) {
				stmt.close();
				con.close();
				return false;
			} else {
				String query = "INSERT INTO GEBRUIKER (username, password, email, credits) VALUES (?, ?, ?, 0)";
				PreparedStatement ps = con.prepareStatement(query);
				ps.setString(1, unm);
				ps.setString(2, pwd);
				ps.setString(3, eml);

				ps.executeUpdate();

				ps.close();
				stmt.close();
				con.close();
				return true;
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
			return false;
		} catch(Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	public static String loginUser(String unm, String pwd, Connection con) {
		try {
			PreparedStatement ps = con.prepareStatement("SELECT COUNT(*) FROM gebruiker WHERE username=? AND password=? AND blocked=0");
			ps.setString(1, unm);
			ps.setString(2, pwd);
			ResultSet rs = ps.executeQuery();
			if(!(rs.next() && rs.getInt(1) == 1)) {
				ps.close();
				con.close();
				return null;
			} else {
				ps.close();
			}
		} catch(SQLException ex) {
			ex.printStackTrace();
		}
		Calendar c = Calendar.getInstance();
		int today = c.get(Calendar.DAY_OF_MONTH) +
				c.get(Calendar.MONTH) +
				c.get(Calendar.YEAR) +
				c.get(Calendar.HOUR) +
				c.get(Calendar.MINUTE) +
				c.get(Calendar.SECOND);
		String loginKey = unm+today;
		int loginHash = loginKey.hashCode();

		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate("UPDATE gebruiker SET loginkey = '"+loginHash+"' WHERE username='"+unm+"'");
			stmt.close();
			con.close();				
			return loginHash+"";
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static User getUserData(String key, Connection con) {
		String userName = getUsername(key, con);
		if(userName.equals("")) {
			return null;
		} else {
			return returnUserData(userName, con);
		}
	}
	
	public static User getUserData(int userID, Connection con) {
		String userName = getUserFromID(userID, ServerServiceImpl.getConnection()).getUserName();
		if(userName.equals("")) {
			return null;
		} else {
			User u = returnUserData(userName, con);
			return u;
		}
	}

	public static User getUserData(String key, String username, Connection con) {
		if(validLogin(key, con)) {
			return returnUserData(username, con);
		} else {
			return null;
		}
	}

	private static User returnUserData(String username, Connection con) {
		User u = null;
		String userName, firstName, prefix, lastName, phoneHome, phoneWork,
		street, streetNr, streetNrAdd, city, zipCode;
		int dbid;
		int gender;
		Date dateOfBirth;
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM gebruiker WHERE username = '" + username + "'" );
			if(rs.next())
			{
				dbid = rs.getInt("USERID");
				userName = rs.getString("USERNAME");
				firstName = rs.getString("FIRSTNAME");
				prefix = rs.getString("PREFIX");
				lastName = rs.getString("LASTNAME");
				gender = rs.getInt("GENDER");
				dateOfBirth = rs.getDate("DATEOFBIRTH");
				phoneHome = rs.getString("PHONEHOME");
				phoneWork = rs.getString("PHONEWORK");
				street = rs.getString("STREET");
				streetNr = rs.getString("STREETNUMBER");
				streetNrAdd = rs.getString("STREETNUMBERADDITION");
				city = rs.getString("CITY");
				zipCode = rs.getString("ZIPCODE");

				u = new User(dbid, userName, firstName, prefix, lastName, gender, dateOfBirth, zipCode, street, streetNr, streetNrAdd, city, phoneHome, phoneWork);
				stmt.close();
				con.close();
			} else {
				stmt.close();
				con.close();
				return null;
			}
		}
		catch(SQLException ex) {
			ex.printStackTrace();
		}
		return u;
	}

	public static boolean setUserData(String loginKey, User u, Connection con) {
		if(validLogin(loginKey, con)) {
			try {
				String query = "UPDATE gebruiker SET firstName=?," +
						"prefix=?," +
						"lastName=?," +
						"gender=?," +
						"dateOfBirth=?," +
						"phoneHome=?," +
						"phoneWork=?," +
						"street=?," +
						"streetNumber=?," +
						"streetNumberAddition=?," +
						"city=?," +
						"zipCode=?" +
						"WHERE username=?";
				PreparedStatement ps = con.prepareStatement(query);
				ps.setString(1, u.getFirstName());
				ps.setString(2, u.getPrefix());
				ps.setString(3, u.getLastName());
				int gender = u.getGender();
				if(gender != 1 && gender != 2) {
					ps.setInt(4, 0);
				} else {
					ps.setInt(4, gender);
				}
				ps.setInt(4, u.getGender());
				Date dateOfBirth = u.getDateOfBirth();
				if(dateOfBirth == null) {
					ps.setDate(5, null);
				} else {
					ps.setDate(5, new java.sql.Date(u.getDateOfBirth().getTime()));
				}
				ps.setString(6, u.getPhoneNumberHome());
				ps.setString(7, u.getPhoneNumberWork());
				ps.setString(8, u.getStreet());
				ps.setString(9, u.getStreetNumber());
				ps.setString(10, u.getStreetNumberAddition());
				ps.setString(11, u.getCity());
				ps.setString(12, u.getZIPCode());
				ps.setString(13, u.getUserName());

				ps.executeUpdate();

				ps.close();
				con.close();
				return true;
			} catch(SQLException ex) {
				ex.printStackTrace();
				return false;
			}
		} else {
			return false;
		}
	}
	
	public static int getUserID(String LoginKey, Connection con){
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT userid FROM gebruiker WHERE loginkey = '" + LoginKey + "'");

			if(rs.next()) {
				return rs.getInt("userid");
			} else {
				return -1;
			}
		} catch(SQLException sqle) {
			System.out.println("SQLException: " + sqle.getMessage());
			sqle.printStackTrace();
			return -1;
		}
	}

	public static String getUsername(String LoginKey, Connection con){
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT username FROM gebruiker WHERE loginkey = '" + LoginKey + "'");

			if(rs.next()) {
				return rs.getString("username");
			} else {
				return "";
			}
		} catch(SQLException sqle) {
			System.out.println("SQLException: " + sqle.getMessage());
			sqle.printStackTrace();
			return "";
		}
	}
	public static ArrayList<String> searchUser(String strSearchInput, Connection con) {
		ArrayList<String> arrUsers = new ArrayList<String>();

		String query = "SELECT username FROM gebruiker WHERE username LIKE '%" + strSearchInput + "%'";
		try {
			Statement stmt = con.createStatement();
			stmt.executeQuery(query);
			ResultSet rs = stmt.getResultSet();
			while(rs.next()) {
				arrUsers.add(rs.getString("username"));
			}
			stmt.close();
			con.close();
			return arrUsers;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public static User getUserFromID(int userID, Connection con) {
		User u = new User();

		String query = "SELECT Username FROM gebruiker WHERE userid = '" + userID + "'";
		try {
			Statement stmt = con.createStatement();
			stmt.executeQuery(query);
			ResultSet rs = stmt.getResultSet();
			if(rs.next()) {
				u.setUserName(rs.getString("username"));
			}
			stmt.close();
			con.close();
			return u;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public static boolean blockUser(String loginKey, String username, Connection con) {
		if(!isAdmin(loginKey, con)) return false;
		String query = "UPDATE Gebruiker SET blocked = 1 WHERE username = ?";
		try {
			PreparedStatement ps = con.prepareStatement(query);
			ps.setString(1, username);
			ps.executeUpdate();
			ps.close();
			con.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public static boolean isAdmin(String loginKey, Connection con) {
		String query = "SELECT admin FROM Gebruiker WHERE loginkey = ?";
		boolean b = false;
		try {
			PreparedStatement ps = con.prepareStatement(query);
			ps.setString(1, loginKey);
			ResultSet rs = ps.executeQuery();
			if(rs.next()) {
				if(rs.getInt(1) == 1) {
					b = true;
				}
			}
			ps.close();
			return b;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
}
